package com.briup.ch02;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yuxi
 * @version 1.0
 * @date 2025/11/6
 * @Description:
 */
public class PrepareStatementTest {
    //普通Statement操作步骤注册驱动，创建数据库连接对象，创建数据库操作对象Statement，while【用executeUpdate和executeQuery执行sql语句，处理结果集】，关闭资源
    //PrepareStatement操作步骤
    // 注册驱动，创建数据库连接对象，（创建PreparedStatement对象，携带？sql语句），
    // 设置参数setXXX(第几个问好)，用executeUpdate和executeQuery执行同构的SQL语句,处理结果集，关闭资源
    // 优点：减少sql注入攻击，提高数据库性能，减少网络流量，提高代码可读性

    //连接4要素
    private String driverClass="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/briup";
    private String username="briup";
    private String password="briup";
    //资源对象
    Connection conn=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    //连接数据库
    @Before
    public void canDo(){
        try{
            Class.forName(driverClass);
            conn= DriverManager.getConnection(url,username,password);
            pstmt=conn.prepareStatement("select count(*) from user where name=? and password=?");
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //关闭数据库资源
    @After
    public void close(){
        try{
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
            System.out.println("数据库资源关闭成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void login() throws SQLException {
        List<User> userList=new ArrayList<>();
        userList.add(new User("admin","admin"));
        userList.add(new User("admin","user123"));
        userList.add(new User("user123","' or '1'='1'"));
        for(User user:userList){
            try {
                //设置参数
                this.pstmt.setString(1, user.getName());//第一个？
                this.pstmt.setString(2, user.getPassword());//第二个？
                //执行查询
                rs=this.pstmt.executeQuery();
                //处理结果集
                while(rs.next()){
                    int count=rs.getInt(1);
                    if(count>0){
                        System.out.println("登录成功！");
                    }else{
                        System.out.println("用户名或密码错误！");
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

}
class User{
    private String name;
    private String password;
    public User(String name,String password){
        this.name=name;
        this.password=password;
    }
    public String getName(){
        return name;
    }
    public String getPassword(){
        return password;
    }
}